<?php

use yii\db\Schema;
use yii\db\Migration;

class m160118_023140_update_table_to_mdsp extends Migration
{
    public function up()
    {
        $this->alterColumn('{{%solution}}', 'bid_type', "int(1) NOT NULL DEFAULT '1' COMMENT '出价方式， 1 固定出价， 2 cpc优化出价, 3 ctr优化出价'");
        $this->addColumn('{{%solution}}', 'optimize_ctr_target', 'decimal(12,2) not null default "0.00" comment "目标CTR" after optimize_bid_cpm');
        $this->addColumn('{{%solution}}', 'optimize_ctr_cpm', 'decimal(12,2) not null default "0.00" comment "ctr 最高出价 cpm" after optimize_ctr_target');
        $this->dropColumn('{{%solution}}', 'weekday_slot');
        $this->dropColumn('{{%solution}}', 'time_slot');
        $this->addColumn('{{%condition_frequency}}', 'mode', "int(1) not null default 0 comment '0 no limit, 1 uniq device, 2 ip' after is_whitelist");
        $this->alterColumn('{{%creative}}', 'form', "int(1) DEFAULT '0' COMMENT '0 banner, 1 video, 2 mobile'");
        $this->alterColumn('{{%creative_ssp}}', 'form', "int(1) DEFAULT '0' COMMENT '0 banner, 1 video, 2 mobile'");

        $sql = <<<FINE
drop table if exists {{%condition_schedule}};
create table if not exists {{%condition_schedule}} (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `solution_id` int(11) unsigned NOT NULL,
    `is_whitelist` int(1) NOT NULL DEFAULT '0' COMMENT '0 blacklist, 1 whitelist',
    `schedule` text comment '时间排期',
    `status` int(1) NOT NULL DEFAULT '1' COMMENT '1 有效， 0 无效（删除或其他情况）',
    `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
)engine=innodb default charset=utf8;

drop table if exists {{%condition_mobile_network}};
create table if not exists {{%condition_mobile_network}} (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `solution_id` int(11) unsigned NOT NULL,
    `is_whitelist` int(1) NOT NULL DEFAULT '1' COMMENT '0 blacklist, 1 whitelist',
    `network` int(1) not null default 0 comment '0 all, 1 wifi, 2 2/3/4G, 3 others',
    `status` int(1) NOT NULL DEFAULT 1 COMMENT '1 有效， 0 无效（删除或其他情况）',
    `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
)engine=innodb default charset=utf8;

drop table if exists {{%condition_mobile_carrier}};
create table if not exists {{%condition_mobile_carrier}} (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `solution_id` int(11) unsigned NOT NULL,
    `is_whitelist` int(1) NOT NULL DEFAULT '1' COMMENT '0 blacklist, 1 whitelist',
    `carrier` int(1) not null default '0' comment '0 all, 1 mobile, 2 unicom, 3 telecom, 4 others',
    `status` int(1) NOT NULL DEFAULT '1' COMMENT '1 有效， 0 无效（删除或其他情况）',
    `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
)engine=innodb default charset=utf8;

drop table if exists {{%condition_mobile_os}};
create table if not exists {{%condition_mobile_os}} (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `solution_id` int(11) unsigned NOT NULL,
    `is_whitelist` int(1) NOT NULL DEFAULT '1' COMMENT '0 blacklist, 1 whitelist',
    `os` int(1) not null default '0' comment '0 all, 1 android, 2 ios, 3 others',
    `status` int(1) NOT NULL DEFAULT '1' COMMENT '1 有效， 0 无效（删除或其他情况）',
    `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
)engine=innodb default charset=utf8;

drop table if exists {{%condition_mobile_os_version}};
create table if not exists {{%condition_mobile_os_version}} (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `solution_id` int(11) unsigned NOT NULL,
    `is_whitelist` int(1) NOT NULL DEFAULT '0' COMMENT '0 blacklist, 1 whitelist',
    `os_version` int(1) not null default '0' comment '操作系统os版本, android 和 ios version 长度不一致',
    `status` int(1) NOT NULL DEFAULT '1' COMMENT '1 有效， 0 无效（删除或其他情况）',
    `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
)engine=innodb default charset=utf8;

drop table if exists {{%condition_mobile_brand}};
create table if not exists {{%condition_mobile_brand}} (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `solution_id` int(11) unsigned NOT NULL,
    `is_whitelist` int(1) NOT NULL DEFAULT '0' COMMENT '0 blacklist, 1 whitelist',
    `brand` int(1) not null default '0' comment 'htc, xiaomi, etc.',
    `status` int(1) NOT NULL DEFAULT '1' COMMENT '1 有效， 0 无效（删除或其他情况）',
    `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
)engine=innodb default charset=utf8;

drop table if exists {{%condition_mobile_adspace_type}};
create table if not exists {{%condition_mobile_adspace_type}} (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `solution_id` int(11) unsigned NOT NULL,
    `is_whitelist` int(1) NOT NULL DEFAULT '1' COMMENT '0 blacklist, 1 whitelist',
    `adspace_type` int(1) not null default '0' comment '0 all, 1 mobile web, 2 android app, 3 ios app',
    `status` int(1) NOT NULL DEFAULT '1' COMMENT '1 有效， 0 无效（删除或其他情况）',
    `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
)engine=innodb default charset=utf8;

drop table if exists {{%mobile_adspace}};
create table if not exists {{%mobile_adspace}} (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `ad_network_id` int(10) NOT NULL,
    `media_id` int(10) NOT NULL DEFAULT '0',
    `format_list` char(20) NOT NULL COMMENT '允许广告形式集合，逗号分割，  1 static-pic, 2 dynamic-pic, 3 swf, 4 txt, 5 flv, 6 mp4',
    `ad_network_adspace_id` varchar(64) DEFAULT NULL,
    `name` varchar(64) DEFAULT NULL,
    `base_price` varchar(32) NOT NULL COMMENT '广告位底价，按 CPM 计算',
    `deal_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'deal id',
    `width` int(10) unsigned NOT NULL COMMENT '宽',
    `height` int(10) unsigned NOT NULL COMMENT '高',
    `position` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0 未知， 1一屏，2二屏，3三屏，4其他',
    `type` int(2) DEFAULT '1' COMMENT '1 web广告，2 in app-android, 3 in app-iOS',
    `size_form` int(2) NOT NULL DEFAULT '1' COMMENT '广告位形式，1 web banner, 0 下部浮层， 2 app banner, 3 开屏，4 插屏，5 信息流',
    `container` int(2) NOT NULL DEFAULT '1' COMMENT '1 div, 2 iframe',
    `category_id` int(11) unsigned not null default 0 COMMENT '0 无效 web 有效，广告位页面分类 id',
    `app_category` int(4) DEFAULT '0' COMMENT 'in app广告位有效，app 分类，apple store 或者 google play 分类',
    `adspace_page_url` text COMMENT '广告位所在页面 URL',
    `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0正常，1已暂停，2已归档',
    `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    PRIMARY KEY (`id`)
)ENGINE=InnoDB AUTO_INCREMENT=2000000 DEFAULT CHARSET=utf8;

drop table if exists {{%mobile_app_category}};
create table if not exists {{%mobile_app_category}} (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `ad_network_id` int(10) NOT NULL,
    `pid` int(11) DEFAULT NULL COMMENT '父id',
    `market` varchar(10) DEFAULT NULL COMMENT '1 google, 2 apple',
    `market_id` varchar(10) DEFAULT NULL COMMENT 'google play id',
    `category` varchar(64) DEFAULT NULL COMMENT '英文名称',
    `name` varchar(64) DEFAULT NULL COMMENT '中文名称',
    `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
FINE;

        $this->execute($sql);
    }

    public function down()
    {
        $this->dropColumn('{{%solution}}', 'optimize_ctr_target');
        $this->dropColumn('{{%solution}}', 'optimize_ctr_cpm');
        $this->addColumn('{{%solution}}', 'weekday_slot', "varchar(13) NOT NULL DEFAULT '0,1,2,3,4,5,6' COMMENT '周几' after end_date");
        $this->addColumn('{{%solution}}', 'time_slot', "text COMMENT '小时等时间段, 如 00,11 表示 0 点到 12点' after weekday_slot");
        $this->dropColumn('{{%condition_frequency}}', 'mode');
        $this->dropTable('{{%condition_schedule}}');
        $this->dropTable('{{%condition_mobile_network}}');
        $this->dropTable('{{%condition_mobile_carrier}}');
        $this->dropTable('{{%condition_mobile_os}}');
        $this->dropTable('{{%condition_mobile_os_version}}');
        $this->dropTable('{{%condition_mobile_brand}}');
        $this->dropTable('{{%condition_mobile_adspace_type}}');
        $this->dropTable('{{%mobile_adspace}}');
        $this->dropTable('{{%mobile_app_category}}');
        return true;
    }

    /*
    // Use safeUp/safeDown to run migration code within a transaction
    public function safeUp()
    {
    }

    public function safeDown()
    {
    }
    */
}
